package cn.fintecher.gis.respository;

import cn.fintecher.gis.entity.PatrolInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

public interface PatrolInfoRepository extends QuerydslPredicateExecutor<PatrolInfo>, JpaRepository<PatrolInfo, String> {

    @Query(value = "select type,count(*) from patrol_info where status='COMPLETE_PATROL' " +
            "and to_char(create_time,'yyyy')=:year group by type",nativeQuery = true)
    public Object[]  getStatisticalType(@Param("year") String year);

    @Query(value = "select type,count(*) from patrol_info where status='COMPLETE_PATROL' and create_time " +
            " between to_date(:start,'YYYY-MM-DD') and to_date(:end,'YYYY-MM-DD') group by type",nativeQuery = true)
    public Object[]  getStatisticalType(@Param("start") String start,
                                        @Param("end") String end);

    @Query(value = "select c.username,count(*) from (select to_char(a.create_time,'yyyy') dateYear," +
            "b.username,a.id from patrol_info as a,operator as b where a.user_id=b.id and " +
            "status='COMPLETE_PATROL') as c where c.dateYear=:year GROUP BY c.username",nativeQuery = true)
    public Object[] getStatisticalUser(@Param("year") String year);

    @Query(value = "select c.username,count(*) from (select b.username,a.id from patrol_info as a," +
            "operator as b where a.user_id=b.id and status='COMPLETE_PATROL' and a.create_time" +
            " between to_date(:start,'YYYY-MM-DD') and to_date(:end,'YYYY-MM-DD')) " +
            "as c GROUP BY c.username",nativeQuery = true)
    public Object[] getStatisticalUser(@Param("start") String start,
                                       @Param("end") String end);
}
